﻿Imports Microsoft.VisualBasic

Namespace Inventory

    Public Class basededados
        Public Sub New()
        End Sub
        Function ExecuteNonQuery(ByVal string_sql As String) As String
            Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString
            Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
            SqlConnection1.ConnectionString = connection_string
            SqlConnection1.Open()
            Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
            SqlCommand1.Connection = SqlConnection1        ' associa o comando a executar com a conexão
            SqlCommand1.CommandText = string_sql            ' define o sql que o comando vai executar
            SqlCommand1.ExecuteNonQuery()                     ' executa do comando na base de dados
            SqlConnection1.Close()                          ' fecha a ligação á base de dados
            Return string_sql
        End Function

        Function ExecuteScalar(ByVal string_sql As String) As String
            Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString
            Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
            SqlConnection1.ConnectionString = connection_string
            SqlConnection1.Open()
            Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
            SqlCommand1.Connection = SqlConnection1        ' associa o comando a executar com a conexão
            SqlCommand1.CommandText = string_sql            ' define o sql que o comando vai executar
            Dim resultado As String = SqlCommand1.ExecuteScalar()                     ' executa do comando na base de dados
            SqlConnection1.Close()                          ' fecha a ligação á base de dados
            Return resultado
        End Function
    End Class

    Public Class companys
        'membros ou campos
        Public id_company As Integer
        Public name As String
        Public website As String
        Public active As Boolean
        'Nascimento da variavel, ou seja o construtor
        Public Sub New()
            id_company = 0
            name = ""
            website = ""
            active = True
        End Sub        
        Function insert_company() As String
            Dim basededados1 = New basededados()
            Dim active_sql As Integer = 0
            If active Then
                active_sql = 1
            End If
            Dim string_sql As String = "INSERT INTO companys(name,website,active) VALUES('" + name + "','" + website + "'," + active_sql.ToString() + ");"
            Return basededados1.ExecuteNonQuery(string_sql)
        End Function
        Function update_company() As String
            Dim basededados1 = New basededados()
            Dim active_sql As Integer = 0
            If active Then
                active_sql = 1
            End If
            Dim string_sql As String = "UPDATE companys SET name='" + name + "',website='" + website + "',active = " + active_sql.ToString() + " WHERE id_company=" + id_company.ToString()
            Return basededados1.ExecuteNonQuery(string_sql)
        End Function

        Function select_company(ByVal sql As String) As String '*****
            Dim connection_string As String = ConfigurationManager.ConnectionStrings("conn").ConnectionString
            Dim SqlConnection1 As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection()
            SqlConnection1.ConnectionString = connection_string
            SqlConnection1.Open()
            Dim SqlCommand1 As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand()
            SqlCommand1.Connection = SqlConnection1
            SqlCommand1.CommandText = sql
            Dim SqlDataReader1 As System.Data.SqlClient.SqlDataReader
            SqlDataReader1 = SqlCommand1.ExecuteReader()
            If SqlDataReader1.Read() Then
                id_company = SqlDataReader1.GetInt32(0)
                name = SqlDataReader1.GetString(1)
                website = SqlDataReader1.GetString(2)
                active = SqlDataReader1.GetBoolean(3)
            End If
            SqlDataReader1.Close()
            SqlConnection1.Close()
            Return sql
        End Function
        Function first_company() As String
            Dim sql As String = "select top 1 id_company,name,website,active from companys ORDER BY id_company ASC"
            Return select_company(sql)  '*****
        End Function
        Function last_company() As String
            Dim sql As String = "select top 1 id_company,name,website,active from companys ORDER BY id_company DESC"
            Return select_company(sql)  '*****
        End Function
        Function next_company() As String
            Dim sql As String = "select top 1 id_company,name,website,active from companys WHERE id_company>" + id_company.ToString() + " ORDER BY id_company ASC"
            Return select_company(sql)  '*****
        End Function
        Function previous_company() As String
            Dim sql As String = "select top 1 id_company,name,website,active from companys WHERE id_company<" + id_company.ToString() + " ORDER BY id_company DESC"
            Return select_company(sql)  '*****
        End Function
        Function delete_company() As String
            Dim sql As String = "delete from companys WHERE id_company=" + id_company.ToString()
            Return select_company(sql)  '*****
        End Function
        Function select_company_by_id() As String
            Dim sql As String = "select id_company,name,website,active from companys WHERE id_company=" + id_company.ToString()
            Return select_company(sql)  '*****
        End Function
    End Class
End Namespace
